const db = require('../db/index')
exports.getscorecourse = (req, res) => {
  const sqlStr = `
  select student.id,name,cname,teachid,grade
  from course,sc,student
  where sc.id=student.id and sc.cid=course.cid and sc.teachid=?`
  db.query(sqlStr, req.query.teachid, (err, results) => {
    if (err) {
      return res.cc(err)
    }
    if (!results.length) {
      return res.cc('获取课程信息失败！')
    }
    res.send({
      status: 0,
      message: '获取课程信息成功！',
      data: results
    })
  })
}
exports.getAllscorecourse = (req, res) => {
  const sqlStr = `
  select student.id AS "sid",student.name,cname,sc.teachid,grade
  from teacher,course,sc,student,tc 
  where sc.id=student.id and sc.cid=course.cid and tc.cid=course.cid and tc.id=teacher.id and teacher.id=?`
  db.query(sqlStr, req.auth.id, (err, results) => {
    if (err) {
      return res.cc(err)
    }
    if (!results.length) {
      return res.cc('获取课程信息失败！')
    }
    res.send({
      status: 0,
      message: '获取课程信息成功！',
      data: results
    })
  })
}
exports.getTeachId = (req, res) => {
  const sqlStr = `
  select teachid
  from teacher,tc
  where teacher.id=tc.id and teacher.id=?`
  db.query(sqlStr, req.auth.id, (err, results) => {
    if (err) {
      return res.cc(err)
    }
    if (!results.length) {
      return res.cc('获取教学班失败！')
    }
    res.send({
      status: 0,
      message: '获取教学班成功！',
      data: results
    })
  })
}
exports.submitScore = (req, res) => {
  const sqlStr = `
  update sc
  set grade=?
  where id=? and teachid=?`
  db.query(sqlStr, [req.body.grade, req.body.id, req.body.teachid], (err, results) => {
    if (err) {
      return res.cc(err)
    }
    if (!results.length) {
      return res.cc('提交成绩失败！')
    }
    res.cc('提交成绩成功！', 0)
  })
}
exports.getAllscore = (req, res) => {
  const sqlStr = `
  select term,sc.cid,cname,credits,teachid,grade
  from course,sc,student
  where sc.id=student.id and sc.cid=course.cid and student.id=? and grade is not null`
  db.query(sqlStr, req.auth.id, (err, results) => {
    if (err) {
      return res.cc(err)
    }
    if (!results.length) {
      return res.cc('获取成绩失败！')
    }
    res.send({
      status: 0,
      message: '获取成绩成功！',
      data: results
    })
  })
}
exports.getScore = (req, res) => {
  let sqlStr = ''
  if (Object.keys(req.query).length === 1) {
    if (Object.keys(req.query)[0] === 'cname') {
      sqlStr = `
      select term,sc.cid,cname,credits,teachid,grade
      from course,sc,student
      where sc.id=student.id and sc.cid=course.cid and student.id=? and grade is not null and cname like "${'%' + req.query.cname + '%'}"`
    } else {
      sqlStr = `
      select term,sc.cid,cname,credits,teachid,grade
      from course,sc,student
      where sc.id=student.id and sc.cid=course.cid and student.id=? and grade is not null and term="${req.query.term}"`
    }
  }
  if (Object.keys(req.query).length === 2) {
    sqlStr = `
    select term,sc.cid,cname,credits,teachid,grade
    from course,sc,student
    where sc.id=student.id and sc.cid=course.cid and student.id=? and grade is not null and cname like "${'%' + req.query.cname + '%'}" and term="${req.query.term}"`
  }
  db.query(sqlStr, req.auth.id, (err, results) => {
    if (err) {
      return res.cc(err)
    }
    if (!results.length) {
      return res.cc('暂无成绩！')
    }
    res.send({
      status: 0,
      message: '获取成绩成功！',
      data: results
    })
  })
}
exports.getNum = (req, res) => {
  const sqlStr = `
  select sum(if(grade<60,1,0)) as 'e',sum(if(grade between 60 and 70,1,0)) 
  as 'd',sum(if(grade between 70 and 80,1,0)) as 'c',sum(if(grade between 80 and 90,1,0)) as 'b',
  sum(if(grade >90,1,0)) as 'a' from sc where teachid=?`
  db.query(sqlStr, req.query.teachid, (err, results) => {
    if (err) {
      return res.cc(err)
    }
    if (!results.length) {
      return res.cc('获取人数失败！')
    }
    res.send({
      status: 0,
      message: '获取人数成功！',
      data: results[0]
    })
  })
}